﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace InsideDL.MISData
{
    public class DataTicket:DataMatrix
    {
        public object GetDataRS(string[] p_csvs, string p_path
            , string[] p_months,string p_currentDay)
        {

            /*
             string p_csvA,string p_csvC
            , string[] p_months,string p_day
             */
            string[] p_para = new[] { p_csvs[1], p_csvs[0], p_currentDay
                ,  p_months[0],p_months[1]};
            string sql = getSQLStatement(p_para);
            return getCSVDataByRecordSet(sql, p_path);
        }

        /// <summary>
        /// 取得債票券SQL Statement
        /// </summary>
        /// <param name="p_para"></param>
        /// <returns></returns>
        protected string getSQLStatement(string[] p_para)
        {
            //ROUND([{3} 月積數]/{2},2)
            string v1 = "{0} 月餘額]  - [{1} 月餘額";

            if (p_para[4].Equals("12"))
            {
                v1 = string.Format("{0} 月餘額", p_para[3]);
            }
            else
            {
                v1 = string.Format(v1, p_para[3], p_para[4]);
            }
            p_para[4] = v1;

            return string.Format(@"
SELECT
    年度 AS 資料年月
	,分行別
	,幣別 
	, 大版塊
	,小版塊
	,科目
    ,ROUND([{3} 月積數]/{2},2) AS 當月平均
FROM {0} AS C  
WHERE
    (
        C.科目 LIKE '1164%'
        OR
        C.科目 LIKE '1168%'
        OR
        C.科目 LIKE '1431%'
        OR
        C.科目 LIKE '1445%'
        OR
        C.科目 LIKE '1751%'
    )

UNION ALL

SELECT
    年度 AS 資料年月
	,分行別
	,幣別 
	, 大版塊
	,小版塊
	,科目
    ,[{4}] AS 當月發生數
FROM {1} AS A
WHERE
    (
        A.科目 LIKE '4501%'
        OR
        A.科目 LIKE '4805%'
        OR
        A.科目 = '4252010000'
        OR
        A.科目 = '5251010000'
        OR
        A.科目 LIKE '5805%'
        OR
        A.科目 = '4252011000'
        OR
        A.科目 = '5251011000' 
        OR
        A.科目 LIKE '5501046%'
    )
", p_para);
        }
    }
}
